var mysql = require('mysql');
var POOL = mysql.createPool({
	host: '127.0.0.1',
    port: 3306,
	user: 'root',
	password: '111111',
	database: 'family'
});
var EXECUTE = function(sql, pms, callback) {
	POOL.getConnection(function (err, conn) {
		if (err) {console.error(err);return;}
		conn.query(sql, pms, function(err, rows) {
			if (err) {console.error(err);return;}
			console.log(sql);
			if (pms) {console.log(pms.toString());}
			console.log(JSON.stringify(rows));
			conn.release();
			callback && callback(rows);
		});
	});
};
var DB = {
	Calendar : {
		getByYang : function(yang, callback) {
			var sql = "SELECT * FROM calendar WHERE yang=?";
			var pms = [yang];
			EXECUTE(sql, pms, callback);
		},
		getByNong : function(nong, callback) {
			var sql = "SELECT * FROM calendar WHERE nong=?";
			var pms = [nong];
			EXECUTE(sql, pms, callback);
		},
		today : function(callback) {
			var sql = "SELECT * FROM calendar  WHERE yang=REPLACE(DATE(NOW()),'-0','-')";
			EXECUTE(sql, null, callback);
		},
		nextNongDay : function(todayId, nong, callback) {
			var sql = "SELECT * FROM calendar  WHERE id > ? AND nong LIKE CONCAT('%',?) LIMIT 1";
			var pms = [todayId, nong];
			EXECUTE(sql, pms, callback);
		}
	},
	Person : {
		insert : function(person) {
			var sql = "insert into person (name, formerName, nickName, sex, birthday, bNong, bNongStr, deathday, dNong, dNongStr, homeAddress, homeTell, workUnits, workAddress, phoneNumber, babaId, mamaId, spouseId, tbId, znId)";
			sql += " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			var pms = [person.name, person.formerName, person.nickName, person.sex, person.birthday, person.bNong, person.bNongStr, person.deathday, person.dNong, person.dNongStr, person.homeAddress, person.homeTell, person.workUnits, person.workAddress, person.phoneNumber, person.babaId, person.mamaId, person.spouseId, person.tbId, person.znId];
			EXECUTE(sql, pms);
		},
		update : function(person, callback) {
			var sql = "update person set name = ?, formerName = ?, nickName = ?, sex = ?, birthday = ?, bNong = ?, bNongStr = ?, deathday = ?, dNong = ?, dNongStr = ?, homeAddress = ?, homeTell = ?, workUnits = ?, workAddress = ?, phoneNumber = ?, babaId = ?, mamaId = ?, spouseId = ?, tbId = ?, znId = ?";
			sql += " where id = ?";
			var pms = [person.name, person.formerName, person.nickName, person.sex, person.birthday, person.bNong, person.bNongStr, person.deathday, person.dNong, person.dNongStr, person.homeAddress, person.homeTell, person.workUnits, person.workAddress, person.phoneNumber, person.babaId, person.mamaId, person.spouseId, person.tbId, person.znId, person.id];
			EXECUTE(sql, pms);
		},
		del : function(id) {
			var sql = "delete from person where id = ?";
			var pms = [id];
			EXECUTE(sql, pms);
		},
		findChildsByBabaId : function(babaId, callback) {
			var sql = "select * from person where babaId = ? order by birthday";
			var pms = [babaId];
			EXECUTE(sql, pms, callback);
		},
		findChildsByMamaId : function(mamaId, callback) {
			var sql = "select * from person where mamaId = ? order by birthday";
			var pms = [mamaId];
			EXECUTE(sql, pms, callback);
		},
		searchListCount : function(params, callback) {
			var sql = "select count(*) as num from person where (name LIKE CONCAT('%',?,'%') OR formerName LIKE CONCAT('%',?,'%') OR nickName LIKE CONCAT('%',?,'%'))";
			var pms = [params.keywords, params.keywords, params.keywords];
			if (params.sex) {sql += " and sex = ?";pms[pms.length] = params.sex;}
			EXECUTE(sql, pms, callback);
		},
		searchList : function(params, callback) {
			var sql = "select * from person where (name LIKE CONCAT('%',?,'%') OR formerName LIKE CONCAT('%',?,'%') OR nickName LIKE CONCAT('%',?,'%'))";
			var pms = [params.keywords, params.keywords, params.keywords];
			if (params.sex) {sql += " and sex = ?";pms[pms.length] = params.sex;}
			sql += " limit ?, ?";
			pms[pms.length] = (params.page - 1) * params.rows;
			pms[pms.length] = params.rows;
			EXECUTE(sql, pms, callback);
		},
		listBirthdayByMonth : function(month, callback) {
			var sql = "select * FROM person WHERE bNong LIKE CONCAT('%-',?,'-%') ORDER BY MONTH(bNong),DAY(bNong)";
			var pms = [month];
			EXECUTE(sql, pms, callback);
		}
	}
};

module.exports = DB;